/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var TgDesignDocModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`design_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '设计模版id',
    //`from_design_id` bigint(20) unsigned DEFAULT NULL COMMENT '来源 根设计模版id（根本身为空）',
    //`tg_id` bigint(20) unsigned NOT NULL COMMENT '来源提纲id',
    //`map_id` bigint(20) unsigned NOT NULL COMMENT '来源模版id',
    //`design_name` varchar(100) DEFAULT NULL COMMENT '设计模版名',
    //`user_id` bigint(20) NOT NULL COMMENT '用户id',
    //`user_type` int(1) NOT NULL DEFAULT '0' COMMENT '0后台',
    //`state` int(2) NOT NULL DEFAULT '10' COMMENT '0未知;10制作中;20提交等待审核;31一级审核通过,32二级审核通过;41一级审核失败,42二级审核失败',
    //`publish` int(1) NOT NULL DEFAULT '0' COMMENT '0未发布; 1已发布',
    //`data` mediumtext COMMENT '模版内容（除page以外的信息）',
    // `finetune_data` mediumtext COMMENT '微调数据',
    //`hash_tag` varchar(100) DEFAULT '' COMMENT '标签',
    //`thumb` text COMMENT '缩略图',
    //`size_id` bigint(20) NOT NULL COMMENT '尺寸',
    //`type_id` bigint(20) NOT NULL COMMENT '分类',
    //`item_id` bigint(20) unsigned NOT NULL COMMENT '用于印品时关联的商品id',
    //`price` bigint(20) NOT NULL DEFAULT '0' COMMENT '设计费用(单位:rmb分)',
    //`order` int(3) NOT NULL DEFAULT '999' COMMENT '显示顺序',
    //`ver` int(10) NOT NULL DEFAULT '1' COMMENT '版本号',
    //`create_time` datetime NOT NULL,
    //`update_time` datetime NOT NULL,
    //`update_by` varchar(50) NOT NULL DEFAULT '',
    //`del_flg` int(1) NOT NULL DEFAULT '0',


};
TgDesignDocModel.prototype = new ItbBaseModel();

// 取得表名
TgDesignDocModel.prototype.getTableName = function(){
    return 'tg_design_doc';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
TgDesignDocModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'design_id='+this.design_id;
}

//------------------------------------------------------------------
/* 获取设计doc信息
 * 参数:design_id
 * 参数:callback:回调
 * */
TgDesignDocModel.prototype.getDocInfoById = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT x1.*, x2.min_tmpl_price FROM '+
            ' (select t.design_id, t.from_design_id, t.map_id, t.tg_id, '+
            ' t.design_name, t.user_id, t.publish, '+
            ' t.data, t.hash_tag, t.type_id, t.order, t.ver, '+
            ' t.size_id, s.width, s.height, p.type_name ' +
            ' ,t.video_size_id, t.video_pos '+// add 2017-12-13 For 视频背景模版
            ' ,t.vip '+ // add 2017-07-21 增加 vip 返回字段
            ' from TG_DESIGN_DOC t, TG_SIZE s, TG_TYPE p '+
            ' where t.del_flg=0 '+
            ' AND s.del_flg=0 AND s.size_id=t.size_id '+
            // ' AND p.del_flg=0 AND p.type_id=t.type_id '+
            '  AND p.type_id=t.type_id '+ // add by wangjun 2018-06-06 移除分类限制
            ' and t.design_id = '+ THIS.design_id +
            ' order by t.order, t.design_id DESC ) x1 '+
            ' LEFT JOIN '+
            ' (SELECT tp.tmpl_id, MIN(tp.price) AS min_tmpl_price '+
            ' FROM itb_tmpl_price tp '+
            ' WHERE tp.del_flg=0 AND tp.type IN (1,2) GROUP BY tp.tmpl_id) x2 '+
            ' ON x2.tmpl_id = x1.design_id ';


        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            callback(error, result);
        });
    });
}
//--------------------------------end-------------------------------

//------------------------------------------------------------------
TgDesignDocModel.prototype.getDocAndPagesInfoByTypeCnt=function(callback,typeM,typeS,typeL){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT COUNT(t.design_id) AS cnt'+
            ' FROM TG_DESIGN_DOC t, TG_SIZE s, TG_TYPE p, TG_DESIGN_PAGE pg '+
            ' WHERE t.del_flg=0 '+
            ' AND s.del_flg=0 AND s.size_id=t.size_id '+
            ' AND p.del_flg=0 AND p.type_id=t.type_id '+
            ' AND pg.del_flg=0 AND pg.page_index=0 AND pg.design_id=t.design_id ';

        if(Number(THIS.publish||'0') == 1) {
            sql += ' AND t.publish=1 ';
        }
        if (typeM>0) {
            sql += ' AND p.parent= '+typeM;
            if(typeS>0) {
                sql += ' AND p.type_id= '+typeS;
            }
        }
        if(typeL){
            sql += ' AND p.top_parent= '+typeL;
        }

        //var sqlValue = [category, code];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            if(items && items.length>0){
                callback(error, items[0].cnt);
            }else{
                callback(error, 0);
            }
        });

    });

}

TgDesignDocModel.prototype.getDocAndPagesInfoByType=function(callback,typeM,typeS,typeL,skipCnt,pageSize){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT t.design_id, t.from_design_id, t.map_id, t.tg_id, '+
            ' t.design_name, t.user_id, t.publish, pg.thumb, '+
            ' t.data, t.hash_tag, t.type_id, t.order, t.ver, '+
            ' t.size_id, s.width, s.height, p.type_name, '+
            ' DATE_FORMAT(t.create_time, "%Y/%m/%d %H:%i:%S") AS create_time, '+
            ' DATE_FORMAT(pg.update_time, "%Y/%m/%d %H:%i:%S") AS update_time '+
            ' FROM TG_DESIGN_DOC t, TG_SIZE s, TG_TYPE p, TG_DESIGN_PAGE pg '+
            ' WHERE t.del_flg=0 '+
            ' AND s.del_flg=0 AND s.size_id=t.size_id '+
            ' AND p.del_flg=0 AND p.type_id=t.type_id '+
            ' AND pg.del_flg=0 AND pg.page_index=0 AND pg.design_id=t.design_id ';

        if(Number(THIS.publish||'0') == 1) {
            sql += ' AND t.publish=1 ';
        }
        if (typeM>0) {
            sql += ' AND p.parent= '+typeM;
            if(typeS>0) {
                sql += ' AND p.type_id= '+typeS;
            }
        }
        if(typeL){
            sql += ' AND p.top_parent= '+typeL;
        }
        sql += ' ORDER BY t.order, pg.update_time DESC ';

        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [category, code];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}
//---------------------------------end-------------------------------

//------------------------------------------------------------------
/* 根据type获取信息
 * */
TgDesignDocModel.prototype.getDesignInfoByType=function(typeL,typeM,typeS,skipCnt,pageSize,callback,bWithoutVip){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = ' SELECT x1.*, '+
            ' CASE WHEN x2.tmpl_price IS NULL THEN "免费" ELSE x2.tmpl_price END AS tmpl_price, '+
            ' IFNULL(st.apply_cnt,0) AS apply_cnt, IFNULL(st.show_apply_cnt,0) AS show_apply_cnt, '+
            ' IFNULL(st.view_cnt,0) AS view_cnt, IFNULL(st.show_view_cnt,0) AS show_view_cnt '+
            ' FROM '+
            ' (SELECT t.design_id, t.from_design_id, t.map_id, t.tg_id, '+
            ' t.design_name, t.user_id, t.publish, pg.thumb, '+
            ' t.data, t.hash_tag, t.type_id, t.order, t.ver, '+
            ' t.size_id, s.width, s.height, p.type_name, p.top_parent, '+
            ' DATE_FORMAT(t.create_time, "%Y/%m/%d %H:%i:%S") AS create_time, '+
            ' t.vip, '+// added 2017-07-20 v6.0
            ' DATE_FORMAT(pg.update_time, "%Y/%m/%d %H:%i:%S") AS update_time, pg.update_time AS update_time_2 '+
            ' FROM TG_DESIGN_DOC t, TG_SIZE s, TG_TYPE p, TG_DESIGN_PAGE pg '+
            ' WHERE t.del_flg=0 AND t.publish=1 '+
            ' AND s.del_flg=0 AND s.size_id=t.size_id '+
            ' AND p.del_flg=0 AND p.type_id=t.type_id AND p.top_parent='+typeL+
            ' AND pg.del_flg=0 AND pg.page_index=0 AND pg.design_id=t.design_id ';

        if (typeM>0) {
            sql += ' AND p.parent= '+typeM;
            if(typeS>0) {
                sql += ' AND p.type_id= '+typeS;
            }
        }
        if(bWithoutVip) {// 不包括vip模版
            sql += ' AND t.vip=0 ';
        }

        sql += ' ) x1 '+
            ' LEFT JOIN '+
            ' (SELECT tp.tmpl_id, concat(TRUNCATE(MIN(tp.price)/100, 2),"元") AS tmpl_price '+
            ' FROM itb_tmpl_price tp '+
            ' WHERE tp.del_flg=0 AND tp.type IN (1,2,6,7,8) GROUP BY tp.tmpl_id) x2 '+
            ' ON x2.tmpl_id = x1.design_id ';

        // 取统计数据 added 2017-5-12
        sql += ' LEFT JOIN itb_tmpl_stats st '+
            ' ON st.del_flg=0 AND st.type=x1.top_parent AND st.tmpl_id=x1.design_id ';

        if(typeM<0) {
            // 约定typeM<0时取[最新]
            sql += ' ORDER BY x1.update_time_2 DESC, x1.order ';
        } else {
            sql += ' ORDER BY x1.order, x1.update_time_2 DESC ';
        }

        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [category, code];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}
//--------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取设计doc信息
 * 参数:map_id: 分页映射模版id
 * 参数:skipIds: 需过滤掉的design_id(逗号链接)
 * 参数:limit: 最大返回记录数
 * 参数:callback:回调
 * */
TgDesignDocModel.prototype.getDocForSwitch = function(callback, limit){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = '';
        if(Number(THIS.topType||0) == 4){
            // 印品
            sql = ' SELECT t.design_id, t.from_design_id, t.map_id, t.tg_id, '+
                ' t.design_name, t.publish, '+
                ' t.hash_tag, t.type_id, t.ver, '+
                ' t.size_id, s.width, s.height, p.type_name, pg.thumb '+
                ' FROM TG_DESIGN_DOC t, TG_SIZE s, TG_TYPE p, TG_DESIGN_PAGE pg, ITB_ITEMS m '+
                ' WHERE t.del_flg=0 AND t.publish=1 AND t.item_id>0 '+// 已发布的,已关联商品的
                ' AND s.del_flg=0 AND s.size_id=t.size_id '+
                ' AND p.del_flg=0 AND p.type_id=t.type_id '+
                ' AND pg.del_flg=0 AND pg.design_id=t.design_id AND pg.page_index=0 '+
                ' AND m.del_flg=0 AND m.design_id=t.design_id AND m.item_id=t.item_id AND m.state=20';// 上架商品

        } else {
            sql = ' SELECT t.design_id, t.from_design_id, t.map_id, t.tg_id, '+
                ' t.design_name, t.publish, '+
                ' t.hash_tag, t.type_id, t.ver, '+
                ' t.size_id, s.width, s.height, p.type_name, pg.thumb '+
                ' FROM TG_DESIGN_DOC t, TG_SIZE s, TG_TYPE p, TG_DESIGN_PAGE pg '+
                ' WHERE t.del_flg=0 AND t.publish=1 '+// 已发布的
                ' AND s.del_flg=0 AND s.size_id=t.size_id '+
                ' AND p.del_flg=0 AND p.type_id=t.type_id '+
                ' AND pg.del_flg=0 AND pg.design_id=t.design_id AND pg.page_index=0 ';
        }

        if(THIS.tg_id) {
            sql += ' AND t.tg_id = '+ THIS.tg_id;
        }
        if(THIS.map_id) {
            sql += ' AND t.map_id = '+ THIS.map_id;
        }
        if(THIS.skipIds) {
            sql += ' AND t.design_id NOT IN ('+THIS.skipIds+') ';
        }
        sql+=' ORDER BY t.design_id DESC, t.update_time DESC ';
        if(limit && Number(limit)>0) {
            sql+=' limit '+ Number(limit);
        }

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            callback(error, result);
        });
    });
}
//--------------------------------end-------------------------------

//------------------------------------------------------------------
/**
 * Design工程获取设计师的设计列表
 * @param bForCnt
 * @param userId
 * @param publish: 是否已经发布(负数:忽略该参数)
 * @param states: 状态(多状态逗号链接)
 * @param designKind: 0:全部; 10:静态设计; 20:视频设计
 * @param skipCnt
 * @param pageSize
 */
function sql_getDocListForDesign(bForCnt, userId, publish, states, designKind, skipCnt, pageSize) {
    // 静态设计
    var sql_10 = ' SELECT t.design_id, t.map_id, t.tg_id, t.design_name, t.state, '+
        ' t.publish, t.thumb AS doc_thumb, t.type_id, t.update_time, '+
        ' s.size_id, s.width, s.height, p.type_name, pg.thumb, '+
        ' 10 AS design_kind '+
        ' FROM TG_DESIGN_DOC t, TG_SIZE s, TG_TYPE p, TG_DESIGN_PAGE pg '+
        ' WHERE t.del_flg=0 AND t.user_id='+userId+
        ' AND s.del_flg=0 AND s.size_id=t.size_id '+
        ' AND p.del_flg=0 AND p.type_id=t.type_id '+
        ' AND pg.del_flg=0 AND pg.design_id=t.design_id AND pg.page_index=0 ';
    if(Number(publish||'0')>=0) {
        sql_10 += ' AND t.publish ='+publish;
    }
    if(states) {
        sql_10 += ' AND t.state IN ('+states+') ';
    }

    // 视频设计
    var sql_20 = ' SELECT vd.design_id, null AS map_id, null AS tg_id, vd.design_name, vd.state, '+
        ' vd.publish, vd.theme_thumb AS doc_thumb, vd.type_id, vd.update_time, '+
        ' ts.size_id, ts.width, ts.height, tt.type_name, vd.theme_thumb AS thumb, '+
        ' 20 AS design_kind '+
        ' FROM itbour.video_design_doc vd '+
        ' INNER JOIN itbour.tg_size ts ON ts.del_flg=0 AND ts.size_id=vd.size_id '+
        ' INNER JOIN itbour.tg_type tt ON tt.del_flg=0 AND tt.type_id=vd.type_id '+
        ' LEFT JOIN yjx.yjx_video_res vr ON vr.del_flg=0 AND vr.res_id=vd.res_low '+
        ' WHERE vd.del_flg=0 AND vd.user_id='+userId;
    if(Number(publish||'0')>=0) {
        sql_20 += ' AND vd.publish ='+publish;
    }
    if(states) {
        sql_20 += ' AND vd.state IN ('+states+') ';
    }

    // 总sql
    var sql = '';
    if(bForCnt) {
        sql += ' SELECT COUNT(*) AS cnt ';
    } else {
        sql += ' SELECT xx.*, IFNULL(r.content,"") AS ng_msg ' ;
    }

    if(designKind==10) {
        // 仅静态设计
        sql += ' FROM ( '+sql_10+' ) xx ';

    } else if(designKind==20) {
        // 仅视频设计
        sql += ' FROM ( '+sql_20+' ) xx ';

    } else {
        // 全部
        sql += ' FROM ( '+sql_10+' UNION ALL '+sql_20+' ) xx ';
    }

    if(!bForCnt) {
        sql += ' LEFT JOIN tg_review_his r ON r.del_flg=0 AND r.design_id=xx.design_id '+
            ' AND r.state=xx.state AND r.kind=xx.design_kind ';
        // 限定取最新review结果
        sql += ' AND r.his_id IN (SELECT max(r2.his_id) AS his_id FROM tg_review_his r2 WHERE r2.del_flg=0 GROUP BY r2.kind, r2.design_id, r2.state) ';
        sql += ' ORDER BY xx.state, xx.update_time DESC ';

        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " LIMIT " + Number(skipCnt) + "," + Number(pageSize);
        }
    }

    return sql;
}

/* 获取用户设计doc列表(design工程用)
 * 参数: user_id: 用户id
 * 参数: publish: 是否已经发布(负数:忽略该参数)
 * 参数: states: 状态(多状态逗号链接)
 * 参数: designKind: 0:全部; 10:静态设计; 20:视频设计
 *
 * 参数: skipCnt
 * 参数: pageSize
 * 参数:callback:回调
 * */
TgDesignDocModel.prototype.getDocListForDesign = function(callback, skipCnt, pageSize){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        //var sql = ' SELECT tt.*, ifnull(r.content,"") AS ng_msg '+
        //    ' FROM ('+
        //    ' SELECT t.design_id, t.from_design_id, t.map_id, t.tg_id, '+
        //    ' t.design_name, t.state, t.publish, t.thumb AS doc_thumb, '+
        //        //' t.data, t.user_id, t.order, '+
        //    ' t.hash_tag, t.type_id, t.update_time, '+
        //    ' t.size_id, s.width, s.height, p.type_name, pg.thumb '+
        //    ' FROM TG_DESIGN_DOC t, TG_SIZE s, TG_TYPE p, TG_DESIGN_PAGE pg '+
        //    ' WHERE t.del_flg=0 AND t.user_id='+THIS.user_id+
        //    ' AND s.del_flg=0 AND s.size_id=t.size_id '+
        //    ' AND p.del_flg=0 AND p.type_id=t.type_id '+
        //    ' AND pg.del_flg=0 AND pg.design_id=t.design_id AND pg.page_index=0 ';
        //if(Number(THIS.publish||'0')>=0) {
        //    sql += ' AND t.publish ='+THIS.publish;
        //}
        //if(THIS.state) {
        //    sql += ' AND t.state IN ('+THIS.state+') ';
        //}
        //sql+=' )tt '+
        //    ' LEFT JOIN TG_REVIEW_HIS r ON r.del_flg=0 AND r.design_id=tt.design_id AND r.state=tt.state '+
        //    ' ORDER BY tt.state, tt.update_time DESC ';
        //
        //if (typeof skipCnt!='undefined' && skipCnt != null
        //    && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
        //    sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        //}

        var sql = sql_getDocListForDesign(false, THIS.user_id, THIS.publish, THIS.states, THIS.designKind,
            skipCnt, pageSize);

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            callback(error, result);
        });
    });
};

TgDesignDocModel.prototype.getDocListCntForDesign = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        //var sql =
        //    ' SELECT Count(t.design_id) AS cnt '+
        //    ' FROM TG_DESIGN_DOC t, TG_SIZE s, TG_TYPE p, TG_DESIGN_PAGE pg '+
        //    ' WHERE t.del_flg=0 AND t.user_id='+THIS.user_id+
        //    ' AND s.del_flg=0 AND s.size_id=t.size_id '+
        //    ' AND p.del_flg=0 AND p.type_id=t.type_id '+
        //    ' AND pg.del_flg=0 AND pg.design_id=t.design_id AND pg.page_index=0 ';
        //if(Number(THIS.publish||'0')>=0) {
        //    sql += ' AND t.publish ='+THIS.publish;
        //}
        //if(THIS.state) {
        //    sql += ' AND t.state IN ('+THIS.state+') ';
        //}

        var sql = sql_getDocListForDesign(true, THIS.user_id, THIS.publish, THIS.states, THIS.designKind);

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0) {
                callback(error, result[0].cnt);
            } else {
                callback(error, 0);
            }

        });
    });
};
//--------------------------------end-------------------------------

//------------------------------------------------------------------
/* 一级审核通过
 * 参数:user_id: 用户id
 * 参数:design_id: 设计模版id
 * 参数:callback:回调
 * */
TgDesignDocModel.prototype.approveDesign1 = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'UPDATE TG_DESIGN_DOC '+
            ' SET state=31, update_time=sysdate(), update_by= '+THIS.update_by+
            ' WHERE del_flg = 0 AND state=20 AND design_id='+ THIS.design_id;

        //var sqlValue = [THIS.update_by, THIS.doc_id];
        //var sql = mysql.format(sql, sqlValue);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            if(error) {
                callback(error);
            } else {
                callback(null);
            }
        });
    });
}
// 二级审核通过
TgDesignDocModel.prototype.approveDesign2 = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'UPDATE TG_DESIGN_DOC '+
            ' SET state=32, update_time=sysdate(), update_by= '+THIS.update_by+
            ' WHERE del_flg = 0 AND state=31 AND design_id='+ THIS.design_id;

        //var sqlValue = [THIS.update_by, THIS.doc_id];
        //var sql = mysql.format(sql, sqlValue);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            if(error) {
                callback(error);
            } else {
                callback(null);
            }
        });
    });
}
//--------------------------------end-------------------------------

//------------------------------------------------------------------
/* 一级审核失败
 * 参数:user_id: 用户id
 * 参数:design_id: 设计模版id
 * 参数:callback:回调
 * */
TgDesignDocModel.prototype.disapproveDesign1 = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'UPDATE TG_DESIGN_DOC '+
            ' SET state=41, update_time=sysdate(), update_by= '+THIS.update_by+
            ' WHERE del_flg = 0 AND state=20 AND design_id='+ THIS.design_id;

        //var sqlValue = [THIS.update_by, THIS.doc_id];
        //var sql = mysql.format(sql, sqlValue);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            if(error) {
                callback(error);
            } else {
                callback(null);
            }
        });
    });
};
// 二级审核失败
TgDesignDocModel.prototype.disapproveDesign2 = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = 'UPDATE TG_DESIGN_DOC '+
            ' SET state=42, update_time=sysdate(), update_by= '+THIS.update_by+
            ' WHERE del_flg = 0 AND state=31 AND design_id='+ THIS.design_id;

        //var sqlValue = [THIS.update_by, THIS.doc_id];
        //var sql = mysql.format(sql, sqlValue);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            if(error) {
                callback(error);
            } else {
                callback(null);
            }
        });
    });
}
//--------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取与特征值最近的设计
 * 参数 tgId:  题纲id
 * 参数 eigen:  特征值,例如："[1,2,3]"
 * 参数 distance: 相似度阀值
 * 参数 mode: 计算模式: 0:逐项计算(默认),1:模糊计算(跳过值为-1项目)
 * 参数:callback:回调
 * */
TgDesignDocModel.prototype.getDesignByEigen = function(tgId, eigen,distance,mode, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
            ' SELECT * '+
            ' FROM tg_design_doc d, '+
            ' tg_type t ' +
            ' WHERE `func_calc_distance_2`(d.`hash_tag`,"'+eigen+'",'+distance+','+Number(mode||'0')+')=1'+
            ' AND d.publish=1'+
            ' AND d.del_flg=0'+
            ' AND d.tg_id='+tgId +
            ' AND t.type_id=d.type_id ' +
            ' AND (CASE WHEN t.top_parent=4 THEN (d.item_id IS NOT NULL AND d.item_id != 0) ELSE 1=1 END) ';

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            callback(error, result);
        });
    });
}

//--------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取与隐藏特征值最近的设计
 * 参数 tgId:  题纲id
 * 参数 eigen:  特征值,例如："[1,2,3]"
 * 参数:callback:回调
 * */
TgDesignDocModel.prototype.getDesignByEigenOfHidden = function(tgId, eigen, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
            ' SELECT * '+
            ' FROM tg_design_doc d, '+
            ' tg_type t ' +
            ' WHERE `func_calc_equal_hidden`(d.`hash_tag`,"'+eigen+'")=1'+
            ' AND d.publish=1'+
            ' AND d.del_flg=0'+
            ' AND d.tg_id='+tgId +
            ' AND t.type_id=d.type_id ' +
            ' AND (CASE WHEN t.top_parent=4 THEN (d.item_id IS NOT NULL AND d.item_id != 0) ELSE 1=1 END) ';

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            callback(error, result);
        });
    });
}

//--------------------------------end-------------------------------

//------------------------------------------------------------------
/* 获取设计doc顶级分类
 * 参数:design_id
 * 参数:callback:回调
 * */
TgDesignDocModel.prototype.getDesignTopType = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT p.top_parent '+
            ' FROM TG_DESIGN_DOC t, TG_TYPE p '+
            ' WHERE t.del_flg=0 AND t.design_id='+ THIS.design_id+
            ' AND p.del_flg=0 AND p.type_id=t.type_id ';

        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result[0].top_parent);
            } else{
                callback(error, 0);
            }

        });
    });
}
//--------------------------------end-------------------------------


//------------------------------------------------------------------
/* 获取设计doc的尺寸的信息
 * 参数:design_id
 * 参数:callback:回调
 * */
TgDesignDocModel.prototype.findSizeInfo = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT s.* '+
            ' FROM TG_DESIGN_DOC t,  '+
            ' 	    TG_SIZE s  '+
            ' WHERE t.del_flg=0  '+
            ' AND t.design_id= '+ THIS.design_id +
            ' AND s.del_flg=0  '+
            ' AND s.size_id=t.size_id  ';


        //var sqlValue = [THIS.type_id];
        //sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result[0]);
            } else{
                callback(error, null);
            }

        });
    });
}
//--------------------------------end-------------------------------


//------------------------------------------------------------------
// 获取sql，
/**
 * 获取的列表
 *
 * @param bForCnt 是否用户
 * @param typeM 二级分类
 * @param colors 颜色
 * @param tags 标签
 * @param withTotalPage 总页数
 * @param pageNum 请求页码
 * @param pageSize 请求页容量
 * @param vipFlg 是否获取 vip 模板, 不传或 false 认为不获取, true 认为获取 vip 模板
 * */
function sql_getFilterListForWebOfPoster  (bForCnt,typeM,colors,tags, withTotalPage, pageNum, pageSize, vipFlg){
    // 表a:查询出模版的基本信息
    var selectSql  = ' distinct '+
                     ' d.design_id AS id  '+
                     ' ,p.`thumb` AS thumb  '+
                     ' ,1 AS typeL  '+
                     ' ,d.design_name AS title ' +
                     ' ,d.vip'; // add 2017-07-21 增加 vip 返回字段

    var fromSql =   '  tg_design_doc d  '+
                    ' ,tg_design_page p '+
                    ' ,tg_type t ';

    var whereSql =  ' d.del_flg=0 '+
                    ' AND d.design_id = p.design_id '+
                    ' AND p.page_index = 0 '+
                    ' AND d.type_id = t.type_id '+
                    ' AND d.publish = 1 ';
    if(!vipFlg) {
        whereSql += 'AND d.vip=0'; // add 2017-07-21 增加 vip 过滤
    }

    if(typeM && typeM != -1){
        whereSql += ( ' AND t.`parent` = '+ typeM);
    }

    // 获取全部海报
    if(typeM == -1) {
        whereSql += ( ' AND t.`top_parent` = '+ 1);
    }

    var orderSql = ' ORDER BY d.create_time desc  ';
    var limitSql = ' LIMIT '+pageNum*pageSize+','+pageSize;
    if(colors && colors.length>0){
        fromSql = fromSql +
                  '  ,tg_keyword k '+
                  '  ,tg_keyword_r kr ';

        whereSql = whereSql +
                    ' AND k.`type`=4 '+
                    ' AND k.key_id in (' +colors.join(',')+ ') '+
                    ' AND k.key_id=kr.key_id '+
                    ' AND kr.doc_id=d.design_id ';
    }
    if(bForCnt){
        // 如果是查询总条数
        var sql_a =     ' SELECT COUNT(*) AS count FROM '+
                        ' ('+
                        '   SELECT '+ selectSql+
                        '   FROM  '+ fromSql+
                        '   WHERE ' + whereSql+
                        ' ) x ';
        return sql_a;
    }else{


        var sql_a =     ' SELECT '+ selectSql+
                        ' FROM  '+ fromSql+
                        ' WHERE ' + whereSql+
                        orderSql+
                        limitSql;

        // 表b:查询出最小价格
        var sql_b =  ' SELECT pr.tmpl_id,  '+
            '  				pr.type,  '+
            '  				MIN(pr.price) AS min_price '+
            '  		FROM itb_tmpl_price pr '+
            '  		WHERE pr.del_flg = 0 '+
            '  		AND pr.type = 1 '+
            '  		GROUP BY pr.tmpl_id ';

        // 表a 左连接 表b
        var sql =
            '  SELECT a.*,  '+
            '         CASE b.min_price WHEN b.min_price THEN CONCAT(ROUND(b.min_price/100,2), "元") ELSE "免费" END AS price '+
            '  FROM ( '+
            sql_a+
            '  ) a '+
            '  LEFT JOIN ( '+
            sql_b+
            '  ) b  '+
            '  ON a.id = b.tmpl_id  ';

        return sql;
    }

}
/**
 *  获取海报列表
 *  参数：typeM 二级分类ID
 *  参数：colors	所需颜色分类 id 数组的 json 字符串, 鉴于网页端可随时发布, 颜色对应可直接写死, 如需多选, 将所选验证传递即可, 如: "[1, 2, 3]"
 *  参数: tags	所需热门标签 id 数组的 json 字符串, 鉴于网页端可随时发布, 标签对应可直接写死, 如需多选, 将所选标签传递即可, 如: "[1, 2]"
 *  参数: withTotalPage 是否需要总页数
 *  参数: pageNum 请求页码, 从 0 开始
 *  参数: pageSize 请求页大小
 *  参数：callback，参数如下：
 *          error：null表示无错误
 *          data：数组，例如：
 *              [
     *                      {
     *                          "id":515,
     *                          "thumb":"http://itbour-generate.oss-cn-hangzhou.aliyuncs.com/image/U3/2017/02/07/153525097_fBkxBZpSet3FHB9K9CWS/0.jpg",
     *                          "typeL":1,
     *                          "title":"大声说爱你情人节祝福模板",
     *                          "desc":"2.00元"
     *                      }
     *          ]
 */

TgDesignDocModel.prototype.getFilterListForWebOfPoster  = function(typeM,colors,tags, withTotalPage, pageNum, pageSize,callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {

        var sql  = sql_getFilterListForWebOfPoster(false,typeM,colors,tags, withTotalPage, pageNum, pageSize );

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result);
            } else{
                callback(error, null);
            }

        });
    });
}
/**
 *  获取海报数量
 *  参数：typeM 二级分类ID
 *  参数：colors	所需颜色分类 id 数组的 json 字符串, 鉴于网页端可随时发布, 颜色对应可直接写死, 如需多选, 将所选验证传递即可, 如: "[1, 2, 3]"
 *  参数: tags	所需热门标签 id 数组的 json 字符串, 鉴于网页端可随时发布, 标签对应可直接写死, 如需多选, 将所选标签传递即可, 如: "[1, 2]"
 *  参数: withTotalPage 是否需要总页数
 *  参数: pageNum 请求页码, 从 0 开始
 *  参数: pageSize 请求页大小
 *  参数：callback，参数如下：
 *          error：null表示无错误
 *          data：数组，例如：
 *              [
 *                      {
     *                          "id":515,
     *                          "thumb":"http://itbour-generate.oss-cn-hangzhou.aliyuncs.com/image/U3/2017/02/07/153525097_fBkxBZpSet3FHB9K9CWS/0.jpg",
     *                          "typeL":1,
     *                          "title":"大声说爱你情人节祝福模板",
     *                          "desc":"2.00元"
     *                      }
 *          ]
 */

TgDesignDocModel.prototype.getFilterListCountForWebOfPoster  = function(typeM,colors,tags, withTotalPage, pageNum, pageSize,callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {

        var sql  = sql_getFilterListForWebOfPoster(true,typeM,colors,tags, withTotalPage, pageNum, pageSize );

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result[0].count);
            }else{
                callback(error, 0);
            }

        });
    });
}
/**获取模板的提纲微调信息
 * 参数: typeL	0 未分类   1 海报  2 多页  3 ppt. 4, 印品, 6, 社交媒体
 * 参数: designId	模板 id
 * 返回结果: callback,参数如下
 *          error：null 表示无错误
 *          data：结构如下：
 *               {
 *                 "bleedHeight": 36,
 *                 "bleedWidth": 36,
 *                 "coreStructure": {},            // 文档核心结构字符串
 *                 "designId": 738,
 *                 "designNm": "红色线型简约名片",
 *                 "pageCount": 2,
 *                 "pageHeight": 709,
 *                 "pageWidth": 1134,
 *                 "printPxW": 1200, // 印刷用像素值
 *                 "printPxH": 1900, // 印刷用像素值
 *                 "productId": 0,
 *                 "productNm": "",
 *                 "textObjects": “",               //  微调初始化字符串
 *                 "typeS": 40101                   // 三级分类
 *               }
 * 注意：返回的是typeS而不是typeL
 *
 * */
TgDesignDocModel.prototype.getTgAndFineTuneInfoOfDesign  = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {

        var sql  =
            ' SELECT d.data AS coreStructure, '+
            ' 		d.design_name AS designNm, '+
            ' 		('+
            ' 			SELECT COUNT(p.page_id)'+
            ' 			FROM tg_design_page p'+
            ' 			WHERE p.`design_id` = '+THIS.design_id+
            ' 			AND p.del_flg = 0'+
            ' 		) AS pageCount, '+
            ' 		d.design_id AS designId, '+
            ' 		0 AS productId, '+
            ' 		NULL AS productNm, '+
            ' 		d.finetune_data AS textObjects, '+
            ' 		d.type_id AS typeS, '+
            ' 		t.parent AS typeM, '+
            ' 		t.top_parent AS typeL, '+
            ' 		s.width AS pageWidth, '+
            ' 		s.height AS pageHeight, '+
            '       ceil(s.width / s.ratio) AS printPxW,  '+// added for v5.6
            '       ceil(s.height / s.ratio) AS printPxH, '+// added for v5.6
            ' 		ceil(s.dpi / 25.4 * s.`bleed`) AS bleedWidth, '+
            ' 		ceil(s.dpi / 25.4 * s.`bleed`) AS bleedHeight ' +
            '       ,d.vip ' + //add 2017-07-21 增加 vip 返回字段
            '       ,d.tg_id '+
            '       ,d.map_id '+
            '       ,d.design_id '+
            '       ,d.size_id '+
            '       ,d.type_id '+
            '       ,d.hash_tag '+
            ' FROM tg_design_doc d, '+
            //' 	tg_map_doc m, '+
            ' 	tg_type t, '+
            ' 	tg_size s'+
            ' WHERE d.`design_id` = '+THIS.design_id+
            ' 	AND d.del_flg = 0 '+
            //' 	AND d.map_id = m.map_id '+
            //' 	AND m.del_flg = 0 '+
            '   AND t.del_flg = 0 AND t.type_id=d.type_id '+
            ' 	AND d.size_id = s.size_id '+
            ' 	AND s.del_flg = 0';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result[0]);
            }else{
                callback(error, null);
            }

        });
    });
}

/**
 * 获取模版应用次数统计数据
 * type: 模版大分类
 * ids: 逗号连接的模版id
 */
TgDesignDocModel.prototype.getTmplApplyCnt = function(type, ids, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        // sql for design
        var sqlDesign =
            ' SELECT x1.design_id AS tmpl_id, x1.item_id, '+
            ' IFNULL(x2.apply_cnt, 0) AS apply_cnt, x1.top_parent AS type '+
            ' FROM '+
            ' (SELECT d.design_id, t.top_parent, d.item_id '+
            ' FROM tg_design_doc d, TG_TYPE t '+
            ' WHERE d.del_flg=0 AND t.del_flg=0 AND d.type_id=t.type_id ';
        if(type==1 || type==2 || type==4 || type==6 || type==7 || type==8 || type==9 || type==1010 || type==1011) {
            sqlDesign += ' AND t.top_parent='+type;

            if(ids && ids.length>0) {
                sqlDesign += ' AND d.design_id IN ('+ids+') ';
            }
        }
        sqlDesign += ' ) x1 '+
            ' LEFT JOIN '+
            ' (SELECT b.design_id, COUNT(b.design_id) AS apply_cnt '+
            ' FROM tg_user_doc b '+
            ' WHERE b.del_flg = 0 AND b.purpose != 1 ';
        if((type==1 || type==2 || type==4 || type==6 || type==7 || type==8 || type==9 || type==1010 || type==1011)
            && ids && ids.length>0) {
            sqlDesign += ' AND d.design_id IN ('+ids+') ';
        }
        sqlDesign += ' GROUP BY b.design_id '+
            ' ) x2 '+
            ' ON x1.design_id=x2.design_id ';


        // sql for PPT
        var sqlPPT =
            ' SELECT x1.tmpl_id, null AS item_id, IFNULL(x2.apply_cnt, 0) AS apply_cnt, 3 AS type '+
            ' FROM '+
            ' (SELECT t.tmpl_id '+
            ' FROM itb_template t '+
            ' WHERE t.del_flg=0 AND t.master=0 ';
        if(type==3 && ids && ids.length>0) {
            sqlPPT += ' AND t.tmpl_id IN ('+ids+') ';
        }
        sqlPPT += ' ) x1 '+
            ' LEFT JOIN '+
            ' (SELECT d.base_doc_id, COUNT(d.base_doc_id) AS apply_cnt '+
            ' FROM itb_document d '+
            ' WHERE d.purpose=0 AND d.del_flg=0 ';
        if(type==3 && ids && ids.length>0) {
            sqlPPT += ' AND d.base_doc_id IN ('+ids+') ';
        }
        sqlPPT +=
            ' GROUP BY d.base_doc_id '+
            ' ) x2 '+
            ' ON x1.tmpl_id=x2.base_doc_id ';


        // main sql
        sql = ' SELECT * FROM ( ';
        if(type==1 || type==2 || type==4 || type==6 || type==7 || type==8 || type==9 || type==1010 || type==1011) {
            sql += sqlDesign;
        } else if(type==3) {
            sql += sqlPPT;
        } else {
            sql += sqlDesign + ' UNION ALL ' + sqlPPT;
        }
        sql += ' ) x ORDER BY x.type, x.tmpl_id DESC ';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            callback(error, result);
        });
    });
}

/**
 * 作者 ： 李琦
 * 获取   一键秀模板应用次数统计 数据
 * type: 模版大分类  单页模板  视频模板
 * ids: 逗号连接的模版id
 */
TgDesignDocModel.prototype.getYjxTmplApplyCnt = function(type, ids, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {

        if (type == null || type.length<=0 || Number(type)<=0){
            return {'error':'NG'};
        }
        var sql ='';
        if(type==1 || type==2 || type==4 || type==6 || type==7 || type==8 || type==9 || type==1010 || type==1011 || type==1030) {
            // for 单页
            sql =  " SELECT ui.uni_item_id, ui.item_type, x2.design_id"+
                "	, IFNULL(x2.apply_cnt, '0') AS apply_cnt, x2.top_parent"+
                " FROM yjx.yjx_uni_items ui"+
                "	INNER JOIN ("+
                "		SELECT v.design_id, v.design_name, v.type_id"+
                "			, IFNULL(x1.apply_cnt, 0) AS apply_cnt, p.parent"+
                "			, p.top_parent"+
                "		FROM itbour.tg_design_doc v"+
                "			INNER JOIN itbour.tg_type p"+
                "			ON v.type_id = p.type_id"+
                "				AND p.del_flg = 0"+
                "			LEFT JOIN ("+
                "				SELECT COUNT(vu.design_id) AS apply_cnt, vu.design_id"+
                "				FROM yjx.yjx_video_user_doc vu"+
                "				WHERE vu.del_flg = 0"+
                "				GROUP BY vu.design_id"+
                "			) x1"+
                "			ON x1.design_id = v.design_id"+
                "		WHERE v.del_flg = 0"+
                "	) x2"+
                "	ON x2.design_id = ui.item_id"+
                " WHERE ui.del_flg = 0"+
                "	AND ui.item_type = 10 AND x2.top_parent="+type;
        } else if(type == 1020) {
            // sql for video test
            sql =  "SELECT "+
                "	ui.uni_item_id, "+
                "	ui.item_type, "+
                "	x2.design_id, "+
                "	IFNULL(x2.apply_cnt, '0') apply_cnt "+
                "FROM "+
                "	yjx.yjx_uni_items ui "+
                "LEFT JOIN ( "+
                "	SELECT "+
                "		v.design_id, "+
                "		v.design_name, "+
                "		v.type_id, "+
                "		IFNULL(x1.apply_cnt, 0) apply_cnt "+
                "	FROM "+
                "		itbour.video_design_doc v "+
                "	LEFT JOIN ( "+
                "		SELECT "+
                "			count(vu.design_id) apply_cnt, "+
                "			vu.design_id "+
                "		FROM "+
                "			yjx.yjx_video_user_doc vu "+
                "		WHERE "+
                "			vu.del_flg = 0 "+
                "		GROUP BY "+
                "			vu.design_id "+
                "	) x1 ON x1.design_id = v.design_id "+
                "	WHERE "+
                "		v.del_flg = 0 "+
                ") x2 ON x2.design_id = ui.item_id "+
                "WHERE "+
                "	ui.del_flg = 0 "+
                "AND ui.item_type = 30 ";
        } else {
            // 单页模板 和 视频模板 联合查询统计模板使用次数 插入和修改 yjx.yjx_uni_tmpl_stats 的show_apply_cnt
            sql =
                " SELECT  x1.* FROM (" +
                " SELECT ui.uni_item_id, ui.item_type, x2.design_id" +
                "	, IFNULL(x2.apply_cnt, '0') AS apply_cnt" +
                " FROM yjx.yjx_uni_items ui" +
                "	LEFT JOIN (" +
                "		SELECT v.design_id, v.design_name, v.type_id" +
                "			, IFNULL(x1.apply_cnt, 0) AS apply_cnt" +
                "		FROM itbour.tg_design_doc v" +
                "			LEFT JOIN (" +
                "				SELECT COUNT(vu.design_id) AS apply_cnt, vu.design_id" +
                "				FROM yjx.yjx_video_user_doc vu" +
                "				WHERE vu.del_flg = 0" +
                "				GROUP BY vu.design_id" +
                "			) x1" +
                "			ON x1.design_id = v.design_id" +
                "		WHERE v.del_flg = 0" +
                "	) x2" +
                "	ON x2.design_id = ui.item_id" +
                " WHERE ui.del_flg = 0" +
                "	AND ui.item_type = 10" +
                " UNION ALL " +
                " SELECT ui.uni_item_id, ui.item_type, x2.design_id" +
                "	, IFNULL(x2.apply_cnt, '0') AS apply_cnt" +
                " FROM yjx.yjx_uni_items ui" +
                "	LEFT JOIN (" +
                "		SELECT v.design_id, v.design_name, v.type_id" +
                "			, IFNULL(x1.apply_cnt, 0) AS apply_cnt" +
                "		FROM itbour.video_design_doc v" +
                "			LEFT JOIN (" +
                "				SELECT COUNT(vu.design_id) AS apply_cnt, vu.design_id" +
                "				FROM yjx.yjx_video_user_doc vu" +
                "				WHERE vu.del_flg = 0" +
                "				GROUP BY vu.design_id" +
                "			) x1" +
                "			ON x1.design_id = v.design_id" +
                "		WHERE v.del_flg = 0" +
                "	) x2" +
                "	ON x2.design_id = ui.item_id" +
                " WHERE ui.del_flg = 0" +
                "	AND ui.item_type = 30" +
                " ) x1 GROUP BY x1.uni_item_id ";
        }
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();
            callback(error, result);
        });
    });
}




//------------------------------------------------------------------

/**
 * 根据模板唯一标识获取模板分类信息
 * @param id 设计唯一标识
 * @param callback 回调
 *             err 错误
 *
 * */
TgDesignDocModel.prototype.getDesignTypeInfo = function(id,callback) {

    pool.pool.getConnection(function(err, connection) {
        // 链接出错
        if(err){
            return callback(err, null);
        }
        var sql  =
            " SELECT t.parent AS typeM, " +
            "        t.top_parent AS typeL, " +
            "        t.type_id AS typeS, " +
            "        t.* " +
            " FROM tg_design_doc d, " +
            "      tg_type t " +
            " WHERE d.design_id = " + id +
            "   AND t.type_id=d.type_id ";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();
            if(error || !result || result.length <= 0) {
                return (err, null);
            }
            return callback(error,result[0]);
        });
    });

};

//------------------------------------------------------------------
/* 获取模版基本信息.
 * @param callback,参数如下：
 *          error
 *          data:数组结构，数组元素如下：
 *                  designId
 *                  designName
 *                  typeL
 *                  typeM
 *                  typeS
 *                  vip
 *                  labels // 标签id
 *                  itemDesc 商品描述
 *                  sort 创建时的时间戳作为排序字段
 * */
TgDesignDocModel.prototype.getBaseInfo=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' select mid.*, '+
            ' 		i.`desc` as itemDesc '+
            ' from ( '+
            ' 	select d.`design_id` as designId,  '+
            ' 	d.`design_name` as designName,  '+
            ' 	t.`top_parent` as typeL,  '+
            ' 	t.`parent` as typeM,  '+
            ' 	t.`type_id` as typeS,  '+
            ' 	d.vip as vip,  '+
            ' 	group_concat(k.`key_id`) as labels, '+
            ' 	d.`item_id` as item_id, '+
            ' 	UNIX_TIMESTAMP(d.`create_time`) as sort '+
            ' 	from tg_design_doc d,  '+
            ' 		tg_type t,   '+
            ' 		tg_keyword_r k  '+
            ' 	where d.`design_id`=  '+ THIS.design_id+
            ' 	and d.del_flg=0  '+
            ' 	and d.`type_id`= t.`type_id`  '+
            ' 	and t.del_flg=0  '+
            ' 	and d.`design_id`=k.`doc_id`  '+
            ' 	and k.del_flg=0  '+
            ' ) mid left join itb_items i '+
            ' on mid.item_id=i.`item_id` '+
            ' and i.del_flg=0 ';

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
};

//------------------------------------------------------------------
/* 获取模版基本信息.
 * @param callback,参数如下：
 *          error
 *          data:数组结构，数组元素如下：
 *                  designId
 *                  designName
 *                  typeL
 *                  typeM
 *                  typeS
 *                  vip
 *                  industries
 *                  scenes
 *                  colors
 *                  styles
 *                  customs
 *                  itemDesc 商品描述
 *                  sort 创建时的时间戳作为排序字段
 *                  data 提纲数据
 *                  ver 版本号
 * */
TgDesignDocModel.prototype.getBaseInfo2=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        //var sql =
        //    '		select a.*, '+
        //    ' 			group_concat(distinct t1.key_id) as industries, '+
        //    ' 			group_concat(distinct t2.key_id) as scenes, '+
        //    ' 			group_concat(distinct t3.key_id) as styles, '+
        //    ' 			group_concat(distinct t4.key_id) as colors, '+
        //    ' 			group_concat(distinct t5.key_id) as customs '+
        //    ' 		from(select mid.*,  '+
        //    '              		i.`desc` as itemDesc  '+
        //    '              from (  '+
        //    '              	select d.`design_id` as designId,   '+
        //    '              	d.`design_name` as designName,   '+
        //    '              	t.`top_parent` as typeL,   '+
        //    '              	t.`parent` as typeM,   '+
        //    '              	t.`type_id` as typeS,   '+
        //    '              	d.vip as vip,   '+
        //    '              	d.`item_id` as item_id,  '+
        //    '              	UNIX_TIMESTAMP(d.`create_time`) as sort  '+
        //    '              	from tg_design_doc d,   '+
        //    '              		tg_type t '+
        //    '              	where d.`design_id`=    '+ THIS.design_id +
        //    '              	and d.del_flg=0   '+
        //    '              	and d.`type_id`= t.`type_id`   '+
        //    '              	and t.del_flg=0   '+
        //    '              ) mid left join itb_items i  '+
        //    '              on mid.item_id=i.`item_id`  '+
        //    '              and i.del_flg=0 ) a  '+
        //    '              left join ( select k1.key_id, k1.doc_id from tg_keyword_r k1, tg_keyword y1 where k1.`key_id`=y1.`key_id` and k1.del_flg=0 and y1.del_flg=0 and y1.`type`=1 ) t1 '+
        //    '              on a.designId=t1.doc_id '+
        //    '              left join ( select k1.key_id, k1.doc_id  from tg_keyword_r k1, tg_keyword y1 where k1.`key_id`=y1.`key_id` and k1.del_flg=0 and y1.del_flg=0 and y1.`type`=2) t2 '+
        //    '              on a.designId=t2.doc_id '+
        //    '              left join ( select k1.key_id, k1.doc_id  from tg_keyword_r k1, tg_keyword y1 where k1.`key_id`=y1.`key_id` and k1.del_flg=0 and y1.del_flg=0 and y1.`type`=3) t3 '+
        //    '              on a.designId=t3.doc_id '+
        //    '              left join ( select k1.key_id, k1.doc_id  from tg_keyword_r k1, tg_keyword y1 where k1.`key_id`=y1.`key_id` and k1.del_flg=0 and y1.del_flg=0 and y1.`type`=4) t4 '+
        //    '              on a.designId=t4.doc_id '+
        //    '              left join ( select k1.key_id, k1.doc_id  from tg_keyword_r k1, tg_keyword y1 where k1.`key_id`=y1.`key_id` and k1.del_flg=0 and y1.del_flg=0 and y1.`type`=10) t5 '+
        //    '              on a.designId=t5.doc_id ';

        var sql =
            ' 	SELECT 	mid.*,  '+
            ' 			i.`desc` AS itemDesc, '+
            ' 			(select group_concat(k.key_id) from `tg_keyword_r` r ,`tg_keyword` k where k.`key_id`=r.`key_id` and k.del_flg=0 and r.del_flg=0 and r.`doc_id`= '+ THIS.design_id + '  and k.type=1 and r.type_id!=3 ) as industries, '+
            ' 			(select group_concat(k.key_id) from `tg_keyword_r` r ,`tg_keyword` k where k.`key_id`=r.`key_id` and k.del_flg=0 and r.del_flg=0 and r.`doc_id`= '+ THIS.design_id + '  and k.type=2 and r.type_id!=3 ) as scenes, '+
            ' 			(select group_concat(k.key_id) from `tg_keyword_r` r ,`tg_keyword` k where k.`key_id`=r.`key_id` and k.del_flg=0 and r.del_flg=0 and r.`doc_id`= '+ THIS.design_id + '  and k.type=4 and r.type_id!=3 ) as colors, '+
            ' 			(select group_concat(k.key_id) from `tg_keyword_r` r ,`tg_keyword` k where k.`key_id`=r.`key_id` and k.del_flg=0 and r.del_flg=0 and r.`doc_id`= '+ THIS.design_id + '  and k.type=3 and r.type_id!=3 ) as styles, '+
            ' 			(select group_concat(k.key_id) from `tg_keyword_r` r ,`tg_keyword` k where k.`key_id`=r.`key_id` and k.del_flg=0 and r.del_flg=0 and r.`doc_id`= '+ THIS.design_id + '  and k.type=10 and r.type_id!=3 ) as customs '+
            ' 	FROM (SELECT d.`design_id` AS designId, d.`design_name` AS designName, t.`top_parent` AS typeL, t.`parent` AS typeM, t.`type_id` AS typeS '+
            ' 			, d.vip AS vip, d.`item_id` AS item_id, UNIX_TIMESTAMP(d.`create_time`) AS sort , d.data AS data , d.ver as ver '+
            ' 		FROM tg_design_doc d, tg_type t '+
            ' 		WHERE d.`design_id` =    '+ THIS.design_id +
            ' 			AND d.del_flg = 0 '+
            ' 			AND d.`type_id` = t.`type_id` '+
            ' 			AND t.del_flg = 0 '+
            ' 		) mid '+
            ' 		LEFT JOIN itb_items i ON mid.item_id = i.`item_id` '+
            ' 			AND i.del_flg = 0 ' ;

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}

//------------------------------------------------------------------
/* 获取搜索信息.
 * @param callback,参数如下：
 *          error
 *          data:数组结构，数组元素如下：
 *                     "typeL":8, // 模版一级分类
 *                     "id":1804, // 如果是印品，则有item_id；否则是design_id
 *                     "vip":0, // 模版一级分类
 *                     "type_desc":"微商",  // 模版三级分类描述
 *                     "name":"黄色简约促销活动微商模版",
 *                     "w":"720", // 模版宽度
 *                     "h":"1280", // 模版高度
 *                     "thumb":"http://xxx/0.jpg", // 商品主题图或者预览图／模版的首图
 *                     "author":"高梦梦",
 *                     "time":"2017-07-27 11:33",
 *                     "useCounts":11,
 *                     "price":"免费" // “免费”或者“vip专享”
 * */
TgDesignDocModel.prototype.getSearchInfo=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =

            " select a.*, "+
            " 	case a.typeL when 4 then "+
            // replace by jack liu 2017-10-24 11:27
            // from
            //" 						(SELECT concat('印刷 ',ROUND(b.min_price/100,2),'元/',b.show, b.`unit` ) "+
            // to
            " 						(SELECT concat('',ROUND(b.min_price/100,2),'元/',b.show, b.`unit` ) "+
            // end
            "              			FROM( "+
            "              			 	SELECT pr.item_id,   "+
            " 									MIN(qp.price) AS min_price,   "+
            " 			             			qp.show, qp.`unit`  "+
            " 			             	FROM itb_print_rel pr,   "+
            " 			             			itb_print_param pa,   "+
            " 			             			itb_qty_price qp , "+
            " 			             			itb_items ii "+
            " 			             	WHERE pa.del_flg = 0  "+
            " 			             	AND pr.del_flg = 0  "+
            " 			             	AND qp.del_flg = 0  "+
            " 			             	AND pa.pp_id = pr.pp_id  "+
            " 			             	AND qp.pp_id = pr.pp_id  "+
            " 							AND pr.item_id =ii.item_id "+
            " 							AND ii.del_flg = 0  "+
            " 							AND ii.design_id=  "+ THIS.design_id+
            " 							LIMIT 0,1  "+
            "  "+
            "              			 )b ) "+
            " 	else "+
            " 		(case  a.vip when 10 then 'VIP专享' else '免费' end) "+
            " 	end as price "+
            " from ( "+
            " select	t.top_parent as typeL,  "+
            "              		case t.top_parent when 4 then d.`item_id` else d.design_id end as id,  "+
            "              		d.`vip` as vip,  "+
            // replace by jack liu 2017-10-24 11:27
            // from
            // "              	t.type_name as type_desc ,  "+
            // to
            "              		case t.top_parent when 4 then tp.type_name else t.type_name end as type_desc,  "+
            // end
            "              		d.`design_name` as `name`,  "+
            "              		func_parse_string_array(get_item_or_temp_thumb(d.`design_id`,2),1) as w,  "+
            "              		func_parse_string_array(get_item_or_temp_thumb(d.`design_id`,2),2) as h,  "+
            "              		func_parse_string_array(get_item_or_temp_thumb(d.`design_id`,2),0) as thumb,  "+
            "              		u.`nickname` as author,  "+
            "              		d.create_time as `time`,  "+
            "              		s.show_apply_cnt as useCounts		  "+
            "              from tg_design_doc d ,  "+
            "              	tg_type t,  "+
            // add by jack liu 2017-10-24 11:27
            "              	tg_type tp,  "+
            // end
            "              	itb_user u,  "+
            "              	itb_tmpl_stats s  "+
            "              where d.`design_id`=  "+ THIS.design_id+
            "              and d.del_flg=0  "+
            "              and d.`type_id`= t.`type_id`  "+
            "              and t.del_flg=0  "+
            // add by jack liu 2017-10-24 11:27
            "              and t.`parent`= tp.`type_id`  "+
            "              and tp.del_flg=0  "+
            // end
            "              and d.user_id=u.user_id  "+
            "              and u.del_flg=0  "+
            "              and s.tmpl_id=d.design_id  "+
            "              and s.del_flg=0  "+
            " ) a ";

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
};



/* 获取模版基本信息.
 *
 *
 * @param designId 设计模板id
 * @param callback,参数如下：
 *          error
 *          data 作品作者,作品图
 * */
TgDesignDocModel.prototype.getDesignDetaile=function(designId,callback){
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        if(err) {
            console.log("TgDesignDocModel.js [getDesignDetaile] getConnection err: "+ err);
            return callback(err, null);
        }
        var sql ="	SELECT	" +
            "	  tdd.design_id AS designId,	" +
            "     tdd.design_name as designName, " +
            "	  tdd.user_id AS userId,	" +
            "	  iu.user_name AS userName,	" +
            "	  iu.nickname,	" +
            "	  iu.head_url AS headUrl,	" +
            "	  tdp.thumb AS thumbs,	" +
            "	  ts.width AS w,	" +
            "	  ts.height AS h,	" +
            "	  ts.size_id AS sizeId,	" +
            "	  DATE_FORMAT(tdd.create_time, '%Y-%m-%d') AS `time`	" +
            "	FROM	" +
            "	  itbour.tg_design_doc tdd,	" +
            "	  itbour.itb_user iu,	" +
            "	  itbour.tg_design_page tdp,	" +
            "	  itbour.tg_size ts     " +
            "	WHERE tdd.design_id = "+designId+"	" +
            "	  AND tdd.user_id = iu.user_id	" +
            "	  AND tdd.design_id = tdp.design_id	" +
            "	  AND tdd.size_id = ts.size_id	" +
            "	  AND tdd.del_flg = 0	" +
            "	  AND iu.del_flg = 0	" +
            "	  AND tdp.del_flg = 0	" +
            "	  AND ts.del_flg = 0	" ;
            console.log(sql);
        // Use the connection
        connection.query(sql, function(error, res) {
            // And done with the connection.
            connection.release();
            // 回调
            return callback(error,res);
        });
    });
};


/**
 * 根据用户 id 获取用户模板列表
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param beForCnt 是否是用于获取个数 true/false
 * @param {Object} options 请求参数
 * @param {Object} options.userId 用户 id
 * @param {Array} options.states 上架状态限制
 * @param {Array} options.state2s 下架状态限制
 * @param {Array} options.publish 发布状态限制
 * @param {Number} options.pageNum 页码
 * @param {Number} options.pageSize 页容量
 * @param {Number} options.withTotalPage 是否获取总页数
 * @return {String} sql 对应的 sql
 */
function sql_getUserDesigns(beForCnt, options) {
    var sql = '';
    if(beForCnt) {
        sql += ' SELECT COUNT(0) AS count ';
    } else {
        sql +=
            ' SELECT tdd.design_id, ' +
            '        tdd.design_name, ' +
            '        tdd.state, ' +
            '        tdd.state2, ' +
            '        tdd.publish, ' +
            '        tt.type_id, ' +
            '        tt.top_parent, ' +
            '        tt.type_name, ' +
            '        ts.width, ' +
            '        ts.height, ' +
            '        tdp.thumb, ' +
            '        10 AS design_kind ';
    }

    sql +=
        ' FROM itbour.tg_design_doc tdd, ' +
        '      itbour.itb_user iu, ' +
        '      itbour.tg_type tt, ' +
        '      itbour.tg_design_page tdp, ' +
        '      itbour.tg_size ts ' +
        ' WHERE iu.user_id = ' + options.userId +
        '   AND iu.del_flg = 0 ' +
        '   AND tt.type_id = tdd.type_id ' +
        '   AND tt.del_flg = 0 ' +
        '   AND tdp.design_id = tdd.design_id ' +
        '   AND tdp.page_index = 0 ' +
        '   AND tdp.del_flg = 0 ' +
        '   AND ts.size_id = tdd.size_id ' +
        '   AND tdd.user_id = iu.user_id ' +
        '   AND tdd.del_flg = 0 ';
    if(options.states && options.states.length > 0) {
        sql += '   AND tdd.state in ( ' + options.states.join(',') + ' ) ';
    }
    if(options.state2s && options.state2s.length > 0) {
        sql += '   AND tdd.state2 in ( ' + options.state2s.join(',') + ' ) ';
    }
    if(options.publish && options.publish.length > 0) {
        sql += '   AND tdd.publish in ( ' + options.publish.join(',') + ' ) ';
    }
    if(!beForCnt) {
        sql += '   ORDER BY tdd.create_time DESC ';
    }
    var skipCnt = options.pageNum * options.pageSize;
    if(!beForCnt && !isNaN(skipCnt)) {
        sql += " limit " + Number(skipCnt) + "," + Number(options.pageSize);
    }
    return sql;
}

/**
 * 根据用户 id 获取用户模板列表
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param {Object} options 请求参数
 * @param {Object} options.userId 用户 id
 * @param {Array} options.states 上架状态限制
 * @param {Array} options.state2s 下架状态限制
 * @param {Array} options.publish 发布状态限制
 * @param {Number} options.pageNum 页码
 * @param {Number} options.pageSize 页容量
 * @param {Number} options.withTotalPage 是否获取总页数
 * @param {Function} callback
 *                      err:
 *                      results : 模板数组
 */
TgDesignDocModel.prototype.getUserDesigns = function (options, callback) {
    pool.pool.getConnection(function(getConErr, connection) {
        if(getConErr) {
            console.log('TgDesignDocModel.prototype.getUserDesigns getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getUserDesigns(false, options);
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            connection.release();
            if(queryErr) {
                console.log('TgDesignDocModel.prototype.getUserDesigns queryErr: ' + queryErr);
                return callback(queryErr)
            }
            return callback(queryErr, results);
        });
    });
};


/**
 * 根据用户 id 获取用户模板列表内模板个数
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param {Object} options 请求参数
 * @param {Object} options.userId 用户 id
 * @param {Array} options.states 上架状态限制
 * @param {Array} options.state2s 下架状态限制
 * @param {Array} options.publish 发布状态限制
 * @param {Number} options.pageNum 页码
 * @param {Number} options.pageSize 页容量
 * @param {Number} options.withTotalPage 是否获取总页数
 * @param {Function} callback
 *                      err:
 *                      count : 模板个数
 */
TgDesignDocModel.prototype.getUserDesignsCnt = function (options, callback) {
    pool.pool.getConnection(function(getConErr, connection) {
        if(getConErr) {
            console.log('TgDesignDocModel.prototype.getUserDesigns getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getUserDesigns(true, options);
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            connection.release();
            if(queryErr || !results || results.length <=0) {
                console.log('TgDesignDocModel.prototype.getUserDesigns queryErr: ' + queryErr);
                return callback(queryErr)
            }
            return callback(queryErr, results[0].count);
        });
    });
};


/**
 * 根据用户 id 获取用户模板列表
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param beForCnt 是否是用于获取个数 true/false
 * @param {Object} options 请求参数
 * @param {Number} options.userId 用户 id
 * @param {Number} options.orgId 组织 id
 * @param {Array} options.states 上架状态限制
 * @param {Array} options.state2s 下架状态限制
 * @param {Array} options.publish 发布状态限制
 * @param {Number} options.pageNum 页码
 * @param {Number} options.pageSize 页容量
 * @param {Number} options.withTotalPage 是否获取总页数
 * @return {String} sql 对应的 sql
 */
function sql_getOrgUserDesigns(beForCnt, options) {
    var sql = '';


    if(beForCnt) {
        sql += ' SELECT COUNT(0) AS count ';
    } else {
        sql +=
            ' SELECT tdd.design_id, ' +
            '        tdd.design_name, ' +
            '        tdd.state, ' +
            '        tdd.state2, ' +
            '        tdd.publish, ' +
            '        tt.type_id, ' +
            '        tt.top_parent, ' +
            '        tt.type_name, ' +
            '        ts.width, ' +
            '        ts.height, ' +
            '        tdp.thumb, ' +
            '        10 AS design_kind ';
    }

    sql +=
        ' FROM itbour.tg_design_doc tdd, ' +
        '      itbour.itb_user iu, ' +
        '      itbour.tg_org_r tor, ' +
        '      itbour.tg_type tt, ' +
        '      itbour.tg_design_page tdp, ' +
        '      itbour.tg_size ts ' +
        ' WHERE iu.del_flg = 0 ' +
        '   AND tdd.del_flg = 0 ' +
        '   AND tor.del_flg = 0 ' +
        '   AND tdd.user_id = iu.user_id ' +
        '   AND iu.user_id = tor.user_id ' +
        '   AND tt.type_id = tdd.type_id ' +
        '   AND tt.del_flg = 0 ' +
        '   AND tdp.design_id = tdd.design_id ' +
        '   AND tdp.page_index = 0 ' +
        '   AND tdp.del_flg = 0 ' +
        '   AND ts.size_id = tdd.size_id ' +
        '   AND ts.del_flg = 0 ' +
        '   AND iu.user_id = tor.user_id ' +
        '   AND tor.org_id = ' + options.orgId;

    if(options.states && options.states.length > 0) {
        sql += '   AND tdd.state in ( ' + options.states.join(',') + ' ) ';
    }
    if(options.state2s && options.state2s.length > 0) {
        sql += '   AND tdd.state2 in ( ' + options.state2s.join(',') + ' ) ';
    }
    if(options.publish && options.publish.length > 0) {
        sql += '   AND tdd.publish in ( ' + options.publish.join(',') + ' ) ';
    }
    if(!beForCnt) {
        sql += '   ORDER BY tdd.create_time DESC ';
    }
    var skipCnt = options.pageNum * options.pageSize;
    if(!beForCnt && !isNaN(skipCnt)) {
        sql += " limit " + Number(skipCnt) + "," + Number(options.pageSize);
    }

    return sql;
}

/**
 * 获取组织内模板列表
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param {Object} options 请求参数
 * @param {Number} options.userId 用户 id
 * @param {Number} options.orgId 组织 id
 * @param {Array} options.states 上架状态限制
 * @param {Array} options.state2s 下架状态限制
 * @param {Array} options.publish 发布状态限制
 * @param {Number} options.pageNum 页码
 * @param {Number} options.pageSize 页容量
 * @param {Number} options.withTotalPage 是否获取总页数
 * @param {Function} callback 回调函数
 *                      error: 查询失败
 *                      results 模板列表
 */
TgDesignDocModel.prototype.getOrgUserDesigns = function (options, callback) {
    pool.pool.getConnection(function(getConErr, connection) {
        if(getConErr) {
            console.log('TgDesignDocModel.prototype.getOrgUserDesigns getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getOrgUserDesigns(false, options);
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            connection.release();
            if(queryErr) {
                console.log('TgDesignDocModel.prototype.getOrgUserDesigns queryErr: ' + queryErr);
                return callback(queryErr)
            }
            return callback(queryErr, results);
        });
    });
};


/**
 * 获取组织内模板列表
 *
 * @author 王俊
 * @date 2018-04-12
 *
 * @param {Object} options 请求参数
 * @param {Number} options.userId 用户 id
 * @param {Number} options.orgId 组织 id
 * @param {Array} options.states 上架状态限制
 * @param {Array} options.state2s 下架状态限制
 * @param {Array} options.publish 发布状态限制
 * @param {Number} options.pageNum 页码
 * @param {Number} options.pageSize 页容量
 * @param {Number} options.withTotalPage 是否获取总页数
 * @param {Function} callback 回调函数
 *                      error: 查询失败
 *                      results 模板列表
 */
TgDesignDocModel.prototype.getOrgUserDesignsCnt = function (options, callback) {
    pool.pool.getConnection(function(getConErr, connection) {
        if(getConErr) {
            console.log('TgDesignDocModel.prototype.getUserDesigns getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql = sql_getOrgUserDesigns(true, options);
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            connection.release();
            if(queryErr || !results || results.length <=0) {
                console.log('TgDesignDocModel.prototype.getUserDesigns queryErr: ' + queryErr);
                return callback(queryErr)
            }
            return callback(queryErr, results[0].count);
        });
    });
};

/*
 * 统计模版发布,审核,审核失败数量
 *
 *
 * @param names  用户id列表
 * @param beginTime  开始时间
 * @param endTime   结束时间
 * @param orgId  用户组织id
 * @param callback,参数如下：
 *          error
 *          data 作品作者,作品图
 * */
TgDesignDocModel.prototype.countPublishDesign=function(names,beginTime,endTime,orgId,callback){
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        if(err) {
            console.log("TgDesignDocModel.js [countPublishDesign] getConnection err: "+ err);
            return callback(err, null);
        }
        var nameLimit = "";
        if(names && names.length>0){
            var name = JSON.stringify(names);
            nameLimit = name.substr(1,name.length-2);
        }
        var sql ="  SELECT  " +
            "     ( " +
            "       res.Published + res.checking + res.fail " +
            "     ) AS total,   " +
            "     res.* " +
            "   FROM    " +
            "     (SELECT   " +
            "       *   " +
            "     FROM  " +
            "       (SELECT " +
            "         COUNT(0) AS fail  " +
            "       FROM    " +
            "         itbour.tg_design_doc tdd,  " +
            "         itbour.itb_user iu, " +
            "         itbour.tg_org_r tor " +
            "       WHERE tdd.state IN (41, 42) " +
            "         AND tdd.del_flg = 0   " +
            "         AND iu.del_flg = 0 " +
            "         AND tor.del_flg = 0 " +
            "         AND tdd.user_id = iu.user_id " +
            "         AND iu.user_id = tor.user_id " +
            "         AND tor.org_id = " + orgId+" ";
        if(names && names.length>0){
            sql+=   "  AND tdd.user_id IN ("+nameLimit+")    " ;
        }
        if(endTime){
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" AND UNIX_TIMESTAMP(tdd.create_time) <="+endTime+"  " ;
        }else{
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" " ;
        }
        sql+=") pf,";


        sql+= "       (SELECT " +
            "         COUNT(0) AS checking  " +
            "       FROM    " +
            "         itbour.tg_design_doc tdd,  " +
            "         itbour.itb_user iu, " +
            "         itbour.tg_org_r tor " +
            "       WHERE tdd.state IN (20, 31 , 32) " +
            "         AND tdd.state2 != 32" +
            "         AND tdd.publish = 0  " +
            "         AND tdd.del_flg = 0   " +
            "         AND iu.del_flg = 0 " +
            "         AND tor.del_flg = 0 " +
            "         AND tdd.user_id = iu.user_id " +
            "         AND iu.user_id = tor.user_id " +
            "         AND tor.org_id = " + orgId+" ";
        if(names && names.length>0){
            sql+=   "         AND tdd.user_id IN ("+nameLimit+") " ;
        }
        if(endTime){
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" AND UNIX_TIMESTAMP(tdd.create_time) <="+endTime+"  " ;
        }else{
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" " ;
        }
        sql += " ) pc,  " ;
        sql+="      (SELECT " +
            "         COUNT(0) AS published " +
            "       FROM    " +
            "         itbour.tg_design_doc tdd,  " +
            "         itbour.itb_user iu, " +
            "         itbour.tg_org_r tor " +
            "       WHERE tdd.publish = 1   " +
            "         AND tdd.state = 32 " +
            "         AND tdd.del_flg = 0   " +
            "         AND iu.del_flg = 0 " +
            "         AND tor.del_flg = 0 " +
            "         AND tdd.user_id = iu.user_id " +
            "         AND iu.user_id = tor.user_id " +
            "         AND tor.org_id = " + orgId+" ";
        if(names && names.length>0){
            sql+=   "         AND tdd.user_id IN ("+nameLimit+") " ;
        }
        if(endTime){
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" AND UNIX_TIMESTAMP(tdd.create_time) <="+endTime+"  " ;
        }else{
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" " ;
        }
        sql+=") pp) res " ;
        console.log(sql);
        // Use the connection
        connection.query(sql, function(error, res) {
            // And done with the connection.
            connection.release();
            // 回调
            return callback(error,res);
        });
    });
};


/*
 * 统计模版下架,下架审核中,下架审核失败数量
 *
 *
 * @param names 名字数组
 * @param beginTime 开始时间
 * @param endTime 结束时间
 * @param orgId  用户组织id
 * @param callback,参数如下：
 *          error
 *          data 作品作者,作品图
 * */
TgDesignDocModel.prototype.countUnPublishDesign=function(names,beginTime,endTime,orgId,callback){
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        if(err) {
            console.log("TgDesignDocModel.js [countPublishDesign] getConnection err: "+ err);
            return callback(err, null);
        }
        var nameLimit = "";
        if(names && names.length>0){
            var name = JSON.stringify(names);
            nameLimit = name.substr(1,name.length-2);
        }
        var sql ="  SELECT  " +
            "     ( " +
            "       res.unPublished + res.checking + res.fail   " +
            "     ) AS total,   " +
            "     res.* " +
            "   FROM    " +
            "   (SELECT " +
            "     * " +
            "   FROM    " +
            "     (SELECT   " +
            "       COUNT(0) AS unPublished " +
            "     FROM  " +
            "       itbour.tg_design_doc tdd,    " +
            "         itbour.itb_user iu, " +
            "         itbour.tg_org_r tor " +
            "     WHERE tdd.publish = 0 " +
            "         AND tdd.state2 = 32 " +
            "         AND tdd.del_flg = 0 " +
            "         AND iu.del_flg = 0 " +
            "         AND tor.del_flg = 0 " +
            "         AND tdd.user_id = iu.user_id " +
            "         AND iu.user_id = tor.user_id " +
            "         AND tor.org_id = " + orgId+" ";
        if(names && names.length>0){
            sql+=   "         AND tdd.user_id IN ("+nameLimit+") " ;
        }
        if(endTime){
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" AND UNIX_TIMESTAMP(tdd.create_time) <="+endTime+"  " ;
        }else{
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" " ;
        }
        sql+=")  unp,   " +
            "     (SELECT   " +
            "       COUNT(0) AS checking    " +
            "     FROM  " +
            "       itbour.tg_design_doc tdd,    " +
            "         itbour.itb_user iu, " +
            "         itbour.tg_org_r tor " +
            "     WHERE tdd.state2 IN (20, 31 , 32)  " +
            "         AND tdd.publish = 1 " +
            "         AND tdd.del_flg = 0 " +
            "         AND iu.del_flg = 0 " +
            "         AND tor.del_flg = 0 " +
            "         AND tdd.user_id = iu.user_id " +
            "         AND iu.user_id = tor.user_id " +
            "         AND tor.org_id = " + orgId+" ";
        if(names && names.length>0){
            sql+=   "         AND tdd.user_id IN ("+nameLimit+") " ;
        }
        if(endTime){
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" AND UNIX_TIMESTAMP(tdd.create_time) <="+endTime+"  " ;
        }else{
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" " ;
        }
        sql+=") uc, " +
            "     (SELECT   " +
            "       COUNT(0) AS fail    " +
            "     FROM  " +
            "       itbour.tg_design_doc tdd,    " +
            "         itbour.itb_user iu, " +
            "         itbour.tg_org_r tor " +
            "     WHERE tdd.state2 IN (41, 42)  " +
            "         AND tdd.del_flg = 0 " +
            "         AND iu.del_flg = 0 " +
            "         AND tor.del_flg = 0 " +
            "         AND tdd.user_id = iu.user_id " +
            "         AND iu.user_id = tor.user_id " +
            "         AND tor.org_id = " + orgId+" ";
        if(names && names.length>0){
            sql+=   "         AND tdd.user_id IN ("+nameLimit+") " ;
        }
        if(endTime){
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" AND UNIX_TIMESTAMP(tdd.create_time) <="+endTime+"  " ;
        }else{
            sql+= " AND UNIX_TIMESTAMP(tdd.create_time) >= "+beginTime+" " ;
        }
        sql+=") ufa) res    ";
        console.log(sql);
        // Use the connection
        connection.query(sql, function(error, res) {
            // And done with the connection.
            connection.release();
            // 回调
            return callback(error,res);
        });
    });
};


/**
 * 获取图片信息
 * @param designId  设计id
 * @param state     用来判断是否是删除
 * @param callback
 */
TgDesignDocModel.prototype.getDesignsThumbs = function (designId,state,pageNum,pageSize,callback) {
    pool.pool.getConnection(function(getConErr, connection) {
        if(getConErr) {
            console.log('TgDesignDocModel.prototype.getDesignsThumbs getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql ="	SELECT	" +
                "	  tdd.design_id AS id,	" +
                "	  iu.nickname AS author,	" +
                "	  tdd.create_time AS createTime,	" +
                "	  tdd.design_name AS designDesc,	" +
                "	  tdp.thumb,	" +
                "	  tdp.page_index AS pageIndex	" +
                "	FROM	" +
                "	  itbour.tg_design_doc tdd,	" +
                "	  itbour.tg_design_page tdp,	" +
                "	  itbour.itb_user iu	" +
                "	WHERE tdd.design_id = tdp.design_id	" +
                "	  AND tdd.user_id = iu.user_id	" +
                "	  AND tdp.thumb !=  ''	" +
                "	  AND iu.del_flg = 0	" ;
        if(state !==2){
            sql+= "	  AND tdd.del_flg = 0	" ;
            sql+="	  AND tdp.del_flg = 0	" ;
            sql+="    AND tdd.publish = 1   " ;
        }
        if(designId){
            sql+= " AND tdd.design_id in ("+designId+")" ;
        }
        if(pageNum >=0 && pageSize >0 ){
            sql+= "limit "+pageNum+","+pageSize;
        }
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            connection.release();
            if(queryErr || !results || results.length <=0) {
                console.log('TgDesignDocModel.prototype.getDesignsThumbs queryErr: ' + queryErr);
                return callback(queryErr)
            }
            return callback(queryErr, results);
        });
    });
};


/**
 * 获取图片信息数量
 * @param designId  设计id
 * @param state     用来判断是否是删除
 * @param callback
 */
TgDesignDocModel.prototype.getDesignsThumbsCnt = function (callback) {
    pool.pool.getConnection(function(getConErr, connection) {
        if(getConErr) {
            console.log('TgDesignDocModel.prototype.getDesignsThumbsCnt getConErr: ' + getConErr);
            return callback(getConErr, null);
        }
        var sql ="	SELECT	" +
                "	  count(0) as count	" +
                "	FROM	" +
                "	  itbour.tg_design_doc tdd,	" +
                "	  itbour.tg_design_page tdp,	" +
                "	  itbour.itb_user iu	" +
                "	WHERE tdd.design_id = tdp.design_id	" +
                "	  AND tdd.user_id = iu.user_id	" +
                "	  AND iu.del_flg = 0	" +
                "	  AND tdd.del_flg = 0	" +
                "     AND tdd.publish = 1   " +
                "	  AND tdp.thumb !=  ''	" +
                "	  AND tdp.del_flg = 0	" ;
        console.log(sql);
        connection.query(sql, function(queryErr, results) {
            connection.release();
            if(queryErr || !results || results.length <=0) {
                console.log('TgDesignDocModel.prototype.getDesignsThumbsCnt queryErr: ' + queryErr);
                return callback(queryErr)
            }
            return callback(queryErr, results[0].count);
        });
    });
};



//--------------------------------end-------------------------------



// 导出
module.exports = TgDesignDocModel;